{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb1=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\t+基础数据-商贸.xlsx')\n",
    "仓库档案=wb1.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb1.sheets('存货档案').range('a1').expand('table').options(pd.DataFrame).value\n",
    "存货档案=存货档案.reset_index().loc[:,['存货编码','存货名称','计量单位']]\n",
    "仓库档案['key']=1\n",
    "存货档案['key']=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "合并=pd.merge(仓库档案,存货档案,on='key').drop_duplicates(subset=['仓库编码','仓库名称','存货编码','存货名称'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建一个工作表来存放制作好的 仓库和存货的笛卡尔积\n",
    "wb暂估底稿=xw.Book(R'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\5.暂估入库\\9月暂估入库底稿.xlsx')\n",
    "# wb暂估底稿.sheets[0].range('a1').value=合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "合并.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 收发存汇总表的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "收发存汇总表=pd.read_excel(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\5.暂估入库\\暂估前收发存汇总表 .xlsx',skiprows=5)  # 这个就是导出来收发存汇总表不用动，修改一下路径就可以。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "收发存汇总表=收发存汇总表.transpose() # 转置，以筛选符合条件的列\n",
    "收发存汇总表=收发存汇总表[(收发存汇总表[1]!='平均单价')  & (收发存汇总表[1]!='金额') ]  #筛选出来不等于平均单价 和金额的，最后剩下的就只有数量啦\n",
    "收发存汇总表=收发存汇总表.transpose() #转置回来\n",
    "收发存汇总表.columns=收发存汇总表.loc[0]  # 将第一行提升为标题\n",
    "收发存汇总表=收发存汇总表.iloc[2:] #从第二行开始，之前的0,1两行就不要了\n",
    "收发存汇总表['仓库']=收发存汇总表['仓库'].fillna(method='ffill') #把仓库列填充一下\n",
    "result=收发存汇总表.query('存货编码.notna()').loc[:,['仓库','存货','期初结存','本期入库','本期出库']] # 最后筛选出符合条件的列。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并收发存汇总表的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge = pd.merge(合并,result,left_on=['仓库名称','存货名称'],right_on=['仓库','存货'],how='left').loc[:,['仓库编码','仓库名称','存货编码','存货名称','计量单位','期初结存','本期入库','本期出库']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.count of       仓库编码   仓库名称    存货编码           存货名称 计量单位 期初结存 本期入库 本期出库\n",
       "0       01    总公司  010101            冲锋衣    件  NaN  NaN  NaN\n",
       "1       01    总公司  010102            保温箱    个  NaN  NaN  NaN\n",
       "2       01    总公司  010103           夏季马甲    件  NaN  NaN  NaN\n",
       "3       01    总公司  010104             头盔    个  NaN  NaN  NaN\n",
       "4       01    总公司  010105             帽子    个  NaN  NaN  NaN\n",
       "...    ...    ...     ...            ...  ...  ...  ...  ...\n",
       "22720  110  安康代理商  010802            百纳箱    个  NaN  NaN  NaN\n",
       "22721  110  安康代理商  010390  七色保洁布单条售卖（到家）    条  NaN  NaN  NaN\n",
       "22722  110  安康代理商  010391    螺丝刀46合一（到家）    把  NaN  NaN  NaN\n",
       "22723  110  安康代理商  010901           货运马甲    件  NaN  NaN  NaN\n",
       "22724  110  安康代理商  010130           冬季马甲    件  NaN  NaN  NaN\n",
       "\n",
       "[22725 rows x 8 columns]>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge.to_clipboard()\n",
    "merge.count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并“上月暂估入库数”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1.导入上月暂估入库数到df\n",
    "上月暂估入库数=pd.read_excel(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\5.暂估入库\\红冲8月暂估入库底稿.xlsx',header=0)\n",
    "上月暂估入库数=上月暂估入库数.loc[:,['部门','存货名称','*实收数量']]\n",
    "上月暂估入库数.rename(columns={'*实收数量':'上月暂估入库'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2=pd.merge(merge,上月暂估入库数,left_on=['仓库名称','存货名称'],right_on=['部门','存货名称'],how='left').loc[:,\n",
    "    ['仓库编码','仓库名称','存货编码','存货名称','计量单位','期初结存','本期入库','本期出库','上月暂估入库']\n",
    "    ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "仓库编码      0101010101010101010101010101010101010101010101...\n",
       "仓库名称      总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总公司总...\n",
       "存货编码      0101010101020101030101040101050101060101070101...\n",
       "存货名称      冲锋衣保温箱夏季马甲头盔帽子胸牌短袖加厚冲锋衣防晒衣防晒冰袖冷链短袖蛋糕保温袋高级保温袋黑西...\n",
       "计量单位      件个件个个个件件件套件个件条件件件个件辆套件件套件件个件件个件盒盒盒个盒台件件个个个个个双个...\n",
       "期初结存                                                 132479\n",
       "本期入库                                                  20657\n",
       "本期出库                                                      0\n",
       "上月暂估入库                                             -55257.0\n",
       "dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge2.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并“本月销售数”\n",
    "\n",
    "这个可以是从"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_非赠品=xw.Book(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\3.本月销售\\普通、活动订单.xlsx')\n",
    "wb_赠品=xw.Book(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\3.本月销售\\赠品、推荐币订单.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "本月销售_非赠品=wb_非赠品.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_非赠品=(\n",
    "    本月销售_非赠品.loc[:,['部门','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售_非赠品'})\n",
    "    .groupby(by=['部门','存货名称']).sum().reset_index()\n",
    ")\n",
    "\n",
    "本月销售_赠品=wb_赠品.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_赠品=(\n",
    "    本月销售_赠品.loc[:,['部门','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售_赠品'})\n",
    "    .groupby(by=['部门','存货名称']).sum().reset_index()\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge3\n",
    "merge3=pd.merge(pd.merge(merge2,本月销售_非赠品, left_on=['仓库名称','存货名称'],right_on=['部门','存货名称'],how='left'),\n",
    "                本月销售_赠品, left_on=['仓库名称','存货名称'],right_on=['部门','存货名称'],how='left').drop(['部门_x','部门_y'],axis=1)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge3.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并本期已入库未结算\n",
    "\n",
    "这个涉及到一个从 物料申请表 到 t+编码 和 仓库 的 数据表的 过程。这个其实也简单应该，就是A表 先套装拆件压缩,再编码匹配，再到数据源模版，这个数据源模版就可以用了。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入物料申请管理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_物料申请=xw.Book(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\5.暂估入库\\9月已入库待结算.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "物料申请=wb_物料申请.sheets(\"物料申请列表\").range('a1').expand().options(pd.DataFrame).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "物料申请=(\n",
    "    物料申请.reset_index()\n",
    "    .loc[:,['商品id','商品名称','申请城市','审核状态','收货总件数']]\n",
    "    .query(\"审核状态 != '已结算' and 审核状态!='' \" )\n",
    "    .drop(0) #删除第一行\n",
    "    .assign(城市2=lambda x:x['申请城市'].str.replace('市',\"\"))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "物料申请=物料申请.groupby(by=['城市2','商品名称'])['收货总件数'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 在物料申请管理里匹配编码等，转换为t+存货和仓库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\t+基础数据-商贸.xlsx')\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]\n",
    "\n",
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(物料申请,仓库档案,left_on='城市2',right_on='仓库名称',how='left')\n",
    "合并=pd.merge(合并,存货档案,left_on='商品名称',right_on='存货名称',how='left')\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_物料申请.sheets('编码匹配中间表').range('a1').value=合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并当期已入库未结算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_未结算=wb_物料申请\n",
    "\n",
    "未结算=wb_未结算.sheets('编码匹配中间表').range('a1').expand().options(pd.DataFrame).value\n",
    "\n",
    "未结算=未结算.groupby(by=['仓库名称','存货名称'])['收货总件数'].sum().reset_index()\n",
    "未结算=未结算.rename(columns={'收货总件数':'当月已收货未结算'})\n",
    "merge4=pd.merge(merge3,未结算,left_on=['仓库名称','存货名称'],right_on=['仓库名称','存货名称'],how='left')\n",
    "merge4=(\n",
    "    merge4.set_index(['仓库编码','仓库名称','存货编码','存货名称','计量单位'])\n",
    "    .dropna(how='all')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb暂估底稿.sheets('确定应暂估数').cells.number_format='@'\n",
    "wb暂估底稿.sheets('确定应暂估数').range('a1').value=merge4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge4.to_clipboard()"
   ]
  },
  {
   "attachments": {
    "image.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAAAq0AAAC4CAYAAADJ2NWqAAAgAElEQVR4Ae2dzcrrzLWtfR+ndTqr4yvYuYDTW61gOJ3cwe5sVmN33Qj7DgJ7QeCDQBrxyf/vFwg7i0ACgUDg472G7y6CDsP2sKZKpSG9dkkq2WOBqJ9ZNavqqSFpvlqyvfvXv/7V+DADa8AasAasAWvAGrAGrIGaNbCreXKem08ea8AasAasAWvAGrAGrAFowEGrnzT7Sbs1YA1YA9aANWANWAPVa8BBq0VavUj9F7b/wrYGrAFrwBqwBqwBB60OWh20WgPWgDVgDVgD1oA1UL0GHLRapNWL1H9d+69ra8AasAasAWvAGnDQ6qDVQas1YA1YA9aANWANWAPVa8BBq0VavUj917X/urYGrAFrwBqwBqwBB60OWh20WgPWgDVgDVgD1oA1UL0GHLRapNWL1H9d+69ra8AasAasAWvAGnDQ6qDVQas1YA1YA9aANWANWAPVa8BBq0VavUj917X/urYGrAFrwBqwBqyB3d/+9rfGhxlYA9aANWANWAPWgDVgDdSsgd23337b+DADa8AasAasAWvAGrAGrIGaNeCg1UG7/2ixBqwBa8AasAasAWugeg04aLVIqxdpzX/1eW5+KmENWAPWgDVgDSyjAQetDlodtFoD1oA1YA1YA9aANVC9Bhy0WqTVi9R/wS7zF6w5m7M1YA1YA9ZAzRpw0Oqg1UGrNWANWAPWgDVgDVgD1WvAQatFWr1Ia/6rz3PzUwlrwBqwBqwBa2AZDThoddDqoNUasAasAWvAGrAGrIHqNeCg1SKtXqT+C3aZv2DN2ZytAWvAGrAGataAg1YHrQ5arQFrwBqwBqwBa8AaqF4DRYLWb775pvqFrvmXg/nov1zNx3weOT+tH62fr7/+ugEjH3kG4POI/p69r88vfX6Zz7J8igSt//znP33Si7/QzEeL2nzM55Ebv/Wj9fOHP/zBAasI2sHnEf09e1+fX/r8Mp9l+RQJWv/xj3/4pBdBq/loUZuP+Txy47d+tH5+//vfO2gVQSv4PKK/Z+/r80ufX+azLJ8iQevf//53n/QiaDUfLWrzMZ9HbvzWj9aPg9b8awF8XcJBq9aPzy/zqen6XCRo/etf/+qgVQSt5qNPevMxn0cuitaP1s/vfvc7P2kVT1rB5xH9PXtfn1/6/DKfZfkUCVr/8pe/rHbSf/64a3a7j83nwaDxS/Ppw67Zffy82hzX5IML6pnRh0/Nlxyjzx9XZYP5rc3nctP50nz6+KHZ7aCny/Hh4wCzHMcZ6+rgc9VR4LP78LH5/EVfsJa4odfBp179/Pa3v109aP3Nf3+3+V/f+7fO8Z3v/1fzGxFM8kno3Cn4LKHT0TG+fG4+4l7l60+yH9d7eLz2xPyK93bs6frXnwufj58z1+Ivn5oPQ/f+Ge9ZUeul+RQJWv/85z8nIsvAmwkQArIPHz402Q3DmNg0CHxFYa/JB+Ihow+fvvT3qYKgdW0+3377ufm42zUfPn0Ogf2X5vMnBLHqD6JldL4+n8s6oaN4nn2BdsynqV0/v/nNb6oIWr/z3/FbDL5ufoBA9j/WD1zBJ95k18lfA4/bNfp6/VnxvkUOtVx/OJ/0OsT6tdL1+dQdtJbmUyRo/dOf/rTaSX8W8KfhvyY+f/zQfPq07tPENfngRL6c5Pgr/kPzKX0yVkHQWgOfbED/7bfNFwSuK9841ubDm0HuZpGrY/ul0rX5gEHN+qkzaMV7pl83//kf323+83/0O6dzP2mtI2jFH87pH8gIRtb/34y1z6/0OlLDNSfOaX0+dQetpfkUCVr/+Mc/rhu0fsamZQIyPEHDo/GVA7M1+eDkup3knz/2/6tgZTaY37p8cjeL+AR1zB7bzpNfl0+7ppuOwv+a5OriBX2J/Lp8xvQxZm/5zsXq17/+dYVPWusJWsFnLvbT/a6vk6G5rnt+9c+PGq45kdX6fOoOWkvzKRK0rvkiOwWMJ2K9px0I0vDfLSsHZmvywclFRpd88irFymwwp1X5jL7zM/QHUf9iGi9kJfOr8pEBqrhYhn4lWeR8rcpnA/r51a9+VWHQ+nXzg+/X8XoA+OR0tXTd+X91PnxsPn3OvMa14PmUrnvV8yuz7ng/S+e6Rnl9PuI6PHp9mv8+VppPkaB1zb9UWwGnf6mGYGPlwGxNPjiJW0YQaMJpZTaY36p8Rk/qoKPMBXSJi+SqfMKauzq6vjqx8kv+1s/4TeeXv/xlFUFr94NY322+8/2vqvggFvgscR5PGuPL5/MHQvE5DHwQtIYPOtZy/SG/9DrE+rXS9fnUHbSW5lMkaP3FL36x2kkfBRzz5w9g8V3ElQOzNfngRO5wQRASecR8CFCWvACsymcDQeuqfIImoCN+shkpPpVaw011VT4b0E8tQevtg1j/79+b71TwASy+K1tV0BrOty+f8VmN9D3X8T9SSl+7Vz2/Ag+uq3c/y7Rh2yXS9fnUHbSW5lMkaP3pT39aRdAagzH8V8vtk84rB2Zr8sFJ2z/JL08Pz3xWZoP5rcsnefLcuwCO2ee/iazLp11fX0etbYmbw9AY6/IZ08eYfX6GuGkwQFsrxVde3YLWb75pfvD9f+uU15oXxi19Ux3S6T31NZxz655f/fOjBiZxL2vgM8hk9I/qPt+4thL50nyKBK0/+clP6ghav+V/5eIDWOEv1JUDszX5QHRZQZ/F/LH5vDIbzK8GPr33oa/Baw3fHrA2H164sjrqBfnzXwQ5H6Zr8wGXmvXz85//vLqg9Zv/+a/mO9/79+YHFXxPK/hQS6ulA9fhGs65tc+vdE9qYBLnVAOf7Gd6rt9+M3RtimuYM1+aT5Gg9cc//vFqJ30q4PPmfbx+AIs31IELwpwbFX2vyQfzSBlxbmdWHz40H/gaBXktnK7N5/Y9mx/r/J7W9flcAtEhHVFPa6Xr88HTVLyDWKd+fvazn9UXtH7zTXP+wYHvf7X63MBnLe2241409DF8CKuW70Fe//zq/iFc23WoCj54CLXD/y63H+KDflDX+5rLjd/fiwStP/rRj1Y76XsCvm5eZ6NWDlrX5IOLYo/RTbSXd2HW/h7StflcbhxfGnyn7/mHKM6/tnIJ5rO/Inbj172YtjegsvV18FE6Krve93Ksg0/+F7FqeOe31qD1m2++av7v99b/ntY6glb8EE73F7Hwi3Od+9jC1x2eh3WcX+01Zvh+1rbh3JdIq+Hz5VPnF9Vq+cXC0nyKBK1fffXVakHrEqJ8dAzz0RcT8zGfR86x2vTDV0pq+IMHXPFO2ZrvjNY+dul37h7Rco19azu/amNkPsvev4oErT/84Q8dtIq/gs1Hi9p8zOeRG1F9+rk8tb9800J4t15cIx5Z/1jf//3p/zQ+NIMxhq9sr+/80tfLpffKfPR+lOaz+/z5c+PDDKwBa8AaeE4NOGDVASv4WPvPqX3v6/Pt665pds3Dxw5ufJiBNWANWAPWgDXwdBpo/G+IwNvb25DJ9U3TlOaDq4uDVgfc/qPDGrAGrAFrwBrIa8Dh1yCB0kHZ4EAbNZTm46DVF6n8RcpczMUasAasAWsAGvC/QQKlg7LBgTZqKM3HQWtFF+XjbtfgWPq/lva7XfM2cdzTbtccBtrCh7K/d13wh7nBZ67v1DnTT87HWB3Gfg8f7F863/f6GJuT7Xk9mIu5WAMzaWCjAdMS0y4dlC0x5yXHKM0HCvfrAQNB0dIXwFzAo+aAYEod7w3qVHsEqhiLvz3PcVFPG+ev/Kj1pDb4w3j0xzHRDoEgbGmAmPpAGf3RFv1z9liHtumB9XEeqS32RR7t0D6tjz5gg5+0jctmYg1YA1VqYMkoZ2NjlQ7KNrb80emW5oMrhIPWSgIIBn1TL1oqCIMtFxjxqR8DQKYM6lhmGucCfwzIou/3zjv6VHnMgeOhHcoYC3mMz0Ay5wP2eDAAxvpjPfLszzbwO/XgfOCD/ZHHvBlY53xhLRzXqVlYA9ZA1RoYDU1et0HpoOzZSJbmgyuFg9aFAwgESgwKY8rgJtYxn7ugwZarRx1sMSBjOwRSMdBi/ZQUgRh8pj4YrHGuTHPjTxkHbRjwIUWZzDAW5wBesKfjcD7kOZbm5pSOjzZYP3yl47E/bBibfTku7UzRRu0d2zkd1rfZmI01sKAGni2SKrie0kFZwalV4ao0H6jeQasI/pa6MMZAi4Ha2NgIihggpulQcAXfGCv6njIeAzGMw2AMeQRyfKoInwxso/978hwH4zJY5Li5NAaSU4PC2CfOEfU4MC7mwTLGhW+WYx/uH+bLubMutkN+6vzSfi53dWse5mENLKSBKsKfOidROiirc5X3z6o0HyjeQWsSxK1xIWRAhIAGwdKUOSA4GmrHYCu1I6jCGLEe5bQO80Hb2I751EcMVGOe7d+bwgcD0zgHrInzROCINkhT/2gT2bBt9IU8mcf+rOf4Ko3+MGeMiQN9YFO+4vzi+M7399NMzMQaWFkD98csT9+zdFD2bMBK88GZ4KA1E/gseZFEwMPAEYEOyjEgGpqLCnxgywV08IuxYIt2BFoch3NgGSnasi8DM6TwFfti7tFv9DElj77wCz8M/tiP43E+sOfGSoNWtIc/9KevtMx6rDGuJ/bhWEiHxubc4S/1xTGQ0lesc77dH7MwC2ugIg08WyRVcD2lg7KCU6vCVWk+OCsctIZgZukLJQIbBkYItlBGQMO6ofmMtUH/XGAE/wxa4xiow4HxcoFn7Mc82sIH+w31HVqDqsfcGbQiz/Vy7phDaqc/tMG8YmAa/aEd+sZ5sy/9ogwfHAO+mKcvpOyHFP7QhnX0hbrcATvbOjULa8AaqFYDVYQ/dU6idFBW5yrvn1VpPrhKOGhdMXhAYMTgB0EPAxnkESgNXcQYOKF/7kCQRL/RB/wzWEPKPNpyLkhjn5hHewZgaId8Oj7XEPu9N8/1xfE4bi6Nc0YfltGWa2QQS5+5OWHu0X9cCwNX9o98WUcWHBcpfebS3BxcN6w/szEba2AFDdwfszx9z9JB2bMBK80H6nfQKoK0OS+QCHBiUITAJ5YRJA0Frmg3ZMOc4TsGVVwH+mEcltN26RzYjumjdvoZSxm0Rh7petIyfYIL1oUy+iNwRFsesEUG7Bfbw0cMXplH3+iTfeGPfmHHgTL75VK0Yf97UqyH88n1H+KTtqWftH5KmeucOtYUn6XbUA9TeefakVHN67yHG3SbOxegK7VWMALX2GYLWriHEfvw/GZ5KM3ph22pxciNthIp9hJjRF8YK3fENqP5Z4ukCq6ndFBWcGpVuCrNB+p20Jqc5KMn8IPtcQFBEMMLPy6GOBjYsMybAMrpRQ4XJnVxzPXBunBRi/3Smxb6qfWjPebJOaYp16V8TLFFRmyPNcM/bFgD8pgP7Uwxp3jhjmX2Y9s0jXb4Rl+24do4t7gn6Icx0R7t4kGfuZS+70m5F5wHxuZ841hjvrke9lXt0TY94r6kNuVrCRvXltMJbLk5cA/BMNq5zrQ+ttlanjqJ5wt1pdaZa0NuQ1y3xibOl5zGzhHqDed/jgOvEdG3ymNcjKn2Ivbn+NxPluP1iHm2if0H81WEP3VOonRQVucq759VaT64KjtoTW5OgyduoXa4WOQuQrgRDNXHCyAvRGqeuNDFPmyrLrpD47Mv0rE2Y/boS+W5RvJAiost/LMf8mld7Id8eqA9+Md6+kPKcZCH/8gLfWHnGEjZFz55c0E98qhjH84zlrk2+nhvyjHYD2Xyyc2R7ZDCHg+y5Pqijf3YBmuYenA+9LF0quYJXkPz4VojB+Sxp0o/Q/5qrMcauZ64JjCL5dzc2YY2sEEdmCJl/bOkWBOPKZomB3CJDMBV6S62RZ5+0C+1xTLsnF+ash18jflh2156f8zy9D1LB2XPBqw0H5wJDlpHLgi9E3im9rgYTglkcPEZu3CiTXrBhG9ctJDCnh642KV16Xwwbq4d+8GW9rmHH2+C8MV87oLL+XBMXryZphfwXJl9MU/k0Qb5nI84n5Qv10kfnHfqP50z+70n5Rj0jbGwB/CNfLSn8+T4ORa5uty8on/aySsdj/YlU84PTDAvpByfWmVZpWibYxLralivWsOQjfsV15LmIzf6oX7iuiPTmGefLafUADRFZmAwtqbIh21TLbI+l5Iz90SNCb88MF+MzXMAeRzwAx8s58YcrHu2SKrgekoHZQWnVoWr0nwctE64+AyeyIX74oKCC40ajxfOoTa8wOICFtvgQsWLWax/bx5zVBfPKWuYMibni/XiwNyH+mGtvBCj3RjDIT+oR19c3IfawD/GQxuMOdSO9WgfedF/uj9sPzWFX8wB/jgflIeOOFfMB/3Hxop9YluyxrjwwzLGhm+WY5+l8+DCNWI+yMdjaD5YE/oO2cl8yL6leqwVB+c8VkY7sKTGkEcduKKOfpCyjWIZ29eYp26wlngOc72RHeePPjxydeiD/mzDlG2ZYjwyRJ+YZ5uYwk/sw/bUPMsxjf1H81WEP3VOonRQVucq759VaT640vhJa3LBHT2B3b5zg3omXrjw86aSWxdvDEhz9iXq4k0sBgWYN+eFmxhuUEjTOXGNrGfb6At59Kc/tmV9vPkN5aM/9l8jxfoiM+RzXDA3rBfrQZvcXGGrZV25+b2nLjIZ2sOUQ2wHDtAc6qJOwJYcYUt9vGeOa7WN849r43wiO9p5HpER6qMf1qcpGNIv0uibWkPKfmjPevaLY8d9oT/0QRvY6It9J6X3xyxP37N0UPZswErzwdnioDW5aEw6id2nc6E1s+6NZy4euPHgBsQbW7x5oT69gaJ9Ohe0QdtYD3+xLi2zbXrDi304FlLcYFlm36VSjIt5pgED1kRuDABQRtvIEf3TdaEOB/qhLctLrWmOcciDaxkqc2y0AxcyJF/wABdqj2WkrKOPLaRYI9aDdTJPvcSUvOLauT70y62dPtkupuAVx0MedTzIO7ZBHfcF9dGGfvDPMdkf9ZhzHHs0/2yRVMH1lA7KCk6tClel+UC5DlrfewK7/fsueOZVnBduVLjx4AbEoAM3LN7EeGOKdt6Y0AZteTNDffSHcu5GjHr6RZ43SNTBV5wP8vCJdksfnAvmMLQO3sDZBmvIzZPrZbs0xVi5fluoSzmla0MZe5yuhUzi/qItGdKe9ttSmWtDqg6siW3j+nguxjrkwTzHlDZy5JhkiZR1SHkOR/+s5z5yPzgm+9Bn7DuaryL8qXMSpYOyOld5/6xK88EVyUFr5sI8ehK7T+9mZmb9G/xcTHCDws0pDb54w0rTeKPkzQtzow/k480N9bm584ZH/7wxsj/9IcUccz6WqpvCJs4/zgv14BHrkMe6hvqkbbdWxnpza07XgfWn+xu50J7221qZ68Daho4hLaA99JeumedYWs/ylDHhO57P6ItzDXU4kI/XB47J855jcMxJ6f0xy9P3LB2UPRuw0nxwVjlozVxcJp3I7te7KJtb/0Y1BxPelOJNE3VxrLRMG29iKPMGhrY8cOPL3XBje/jI3cjRN/rkmGukvElz7HRdmH/kx3ZI0RZHrENe9Unb1lrmPqcp9hRHWo9yXEtufyMX2mOfLea5jnT9WAvqsOacDXbygD2eS/HcU0yoS47DMvozz/5sgzHTgzpGyv3luth/UvpskVTB9ZQOygpOrQpXpfngarSJoBUnPk66SSfYQDt1kXnE7yN9sab0wpe7MD0yxljf97LFRY97EeeOC2Msj41r+/165o0q3sCwJ9Q4b0zxhknevIHlyuxHW5pGO3zDF9tgbNg5t7W1gPmlN/G0jPly/ky5xtz8uUa23WKKPUs5jJWjjlI+Q+UtsolzTtcVbWMaB0/qj9dK9Ec+njPRJ/PshzE4DubCfNwL9oEN9fTNuccxOSf6Z99JaRXhT52TKB2U1bnK+2dVmg+u2IsFrTihcHJNOkky7cZOeNoxDo54cvMkjnXpPNAf7dL6ucoYixeZOAbmMZUTfKB97H9PnuxUX/LBmOQY9zS3FuVP2bD+9ED7tG4qJzXWFm1YN25C1CtS3pS4Ht6cuFfkx345lrBRf7TTH1KOgzz8xj2PfpFfe2/S+WGukQXnG9eHPNrxnCIDpuzD8tprTOf+SBlr5rqVH2qAawevqAPalY8t2LgOpHG/kaeNDNL1QCc4Up4oR1ZpP5bRDv3hn5pLObMt06h3zo82jku/sNM2Kb0/Znn6nqWDsmcDVprPbrffNW+PBq6ZEwAnW7xB4MRIT1acQGkbtENfnqBpihM4rYsnIC8i8Bvr0Yd+2SZ3svICwbbpWLEc/ed8jdVh/TkfqFdzTP2iPY60PldW6xpjC3/kijTOE+vI7WVuDmN18Iu5pAf8p3Uo5xiOjbF1O/aRa2c+pwEyIyOyZZrjmdaxL5ghDzvyOR+wcz5I1+TMtafrieW4NsyVfTB3rjW2z+Vz3Ndc971jYx1T1kIuSHE9BJPIkfZ751FLP64jt+esG9I47NBSuhbwBbO0nmX444G2nAN1iTLt6dhsw7khhV/UY0y2Zzv44rij6bNFUgXXUzooKzi1KlyV5gPVzvKkFSdIenKmZZ6U6QmDvunFc6ht2pdj4MTkSYk8DrSFb5zMPIHT/rTn6tM5pW3eW+ZFBX4xbx6oZ57p2Njow/WqedzLFvw4F4wV5xhtbIOUzNV8lI1+YxuyQh33amgvY79ny2PNYIw9x4H80BrBDO1jn6G2Y/UYC3s/1A7zwHhos/a+5PTDeQ+xQP2juuUYW0vjuaXmTr0NMYx25ad2G7We0zHqoHG0ya0j1wftFGOOB79TjvSch28cGAfjw1+si/OExofmHtvd8lWEP3VOonRQVucq759VaT5QeLVBK09wnJw4iZGmB09Snlw8kXlS4sRkHdvghEYd+g5dXNiWKdqlY9F2T4r5YU25vu+ZF/rzYjdlflwH2pLlVLYYC/15QeVFD2vBwbWkvFn/3pR7FPvFOq576h5GP863+/UeFthn7MEQc+o66uE9/t32vn0xN3ObVQP3xyxP37N0UPZswErzwZlebdDKkxABFgMk1g2lDJjQhzdYprDxgB0Hb77I0xZ90x/aoU203ZuHr9xY9BfnxTqVYn04hoLg2DddB8aaypbzRnsyZX/YOA6ZsXxPCn9YD8ZCnv7hG2PDJ2xT1nzP+O7T7qdZmIU18OIaeLZIquB6SgdlBadWhavSfHAmzha0IqBgcIY0V84FTAhQYoDIIAj1DFhyF9HY772BH/1xrLQM33H+cX5sOzXFGFh3Ohb7v3fu8EM2ig/8R0Yocw7szzmkKfxi/WiHuXMc7h/nDDt9pj7eU4Y/jMeDvFHGmJwv1/4e32774jfg8AeWtWAtWAMTNFBF+FPnJEoHZXWu8v5ZleYzW9CauxBMDWYQkDBIQXDEAAk+lQ/0Q1CDNkwZVKEvfSIPn9HG+aIf80g5XpxTtD+Sp2/4wHxQjnNnOa4/HQ9roB+uP20Ty3Ed8Bt9009sH/PoizLapezgB3U4os/Yf2oePrh/6IN9w8H1wZYej445dW5u1z0/zMM8rIEX0MD9McvT9ywdlD0bsNJ8cLbN8qSVQUa8oOWColwAxMCKQQra8GAww3IaPMXxYiCFepaRR38GYbEP6nNlzinaHs3HseLcot+xIBA+IgNwVwEc1/FetvCLsXCke4D5wh9saBfnE9cyJc95cQz0gU8cQ/3R9pExh/y6fpi52ZiNNfBCGni2SKrgekoHZQWnVoWr0nxw1q0etCJQiRdAFaghMIptVZ6BIAMpjMPgZ8hPWs9y7KvGfI+NvtGHc037KxawRR/siyAuZUqb8pfzxX5M0Ya+MecYIIPtFB/0NZTCP/zSF/ziQH16wIeD1unnxBBz15uhNWANDGqgivCnzkmUDsrqXOX9syrNZ/f2Ns/3tDLIiCcBg5C0jkEQ6xkMoX168AlcrMdY6Is65NEfPjkH5tEG+SmBW/TJfqzjPB9NMV/6wJwxL5aZqrkOBafoE33TF9J72aIv/SIFx3QMlIfmFOcwJY95wh/3DuzpH2PwQH2pMafMy236GjUTM7EGnlwD98csT9+zdFD2bMBK89nhhn98e/BpaybYQjCBgCNezNIgBzYGJmPtaM/5oC1Nc3NAG+UDNhwIzGJbBk/pGI+U4zzeG7SiL/oMjY+140jtccz32GJbjAvdRL4cD9xKBJEcA744VpwD89iXEuPRn9O+ZszETKyBF9fAs0VSBddTOigrOLUqXJXmgzNxttcDECCNHWnAgaAnF2zxoqmCLrZhirbwl84BY7IuDawZINEe28Y87Cpo5BxUCh+0M0jjuEwxZhyHQZpiRJ/0wTU+yhZ+MBf4hS8emAvqOC7qMW+krHtvynHQjwFx9IG5cFyMxTXGNs7fz9/szM4asAZuGqgi/KlzEqWDsjpXef+sSvPBWTlL0HoT+0jggmAnBhwIVmI5+kF9DI6iLebh456gCf6nBINxrEfycS2YM47UX1qP+eXapf1YRnsGj+j3CFvMNx0b5RwzjDk0FuemUvTP+Y19MB8cXF+0Od/XkpmYiTVgDdylgftjlqfvWTooezZgpfngDF41aL3rBMoEd/bji7E1YA1YA9aANTCDBp4tkiq4ntJBWcGpVeGqNB+o20Grg+DeE15f+Ge48Ftn1pk1YA1sUAMIPHyYQQ0aQMT68L/SkfTDE6rMgfnoDTEf89EEtNX6MR9NQFutH/PRBLTV+lmWj4NWzbuI1aLWGM3HfDQBbbV+zEcT0Fbrx3w0AW21fpbl46BV8y5itag1RvMxH01AW60f89EEtNX6MR9NQFutn2X5OGjVvItYLWqN0XzMRxPQVuvHfDQBbbV+zEcT0FbrZ1k+Dlo17yJWi1pjNB/z0QS01foxH01AW60f89EEtNX6WZbPJoLWt+P+/L2r+O7V23E4JaTemtNh3+x69Ww2Zk/aXcfaH47NG03n9Opn0N5pfOnx1vXQb/HMNeO8fNLr/Tcf89EEtNX6MR9NQFutH/PRBKC+0PAAABknSURBVLS1tH42E7QOxqLg9XZqDvt9czwe8kHrmD0wPwfIh9M1UH1rTsd9sw+Dj9mDq1u29KbdHG8gM4XXK/OZsoXmoymZj/loAtpq/ZiPJqCt1s+yfDYRtJ4OuybEjT1Cb6djc8LDzFM+aB2ztw5PzWF3aLrPcGNdzLNXro62S/q6os6x6de9Lp+uToZK5jNE5tXPL82FVuuHJPKp+eS5sNZ8SCKfmk+eC2tL89lI0LpvjlP+h30gaCW8oaD2Zn87Nvt9+joAYuFr0DxmvznqZkpvWtd7xaWJvF6Wz8StMx8NynzMRxPQVuvHfDQBbbV+luWzmaD1cDycf2f+/E7rPn0aeoX2aNA60B//xX1+0jtmH9i7lxX1RF4vy2dAL2m1+aREumXz6fJIS+aTEumWzafLIy2ZT0qkWzafLo+0VJrPBoLWt+a4353fK+XD1vN7kpknoqNPUgeCqBvkAbuD1huh92XGeF69lRb1+yZZf2vz0XtkPuajCWir9WM+moC2Wj/L8tlA0JoDgkA288rAQJB083Cn3UHrjeD7MgO8bzyv3nzSa6zmYz6agLZaP+ajCWir9WM+moC2ltbPRoNWvGc6Q9A6+A7mdawx+8Deld60gWHqq57I62X5TNwx89GgzMd8NAFttX7MRxPQVutnWT4bCFrxdVbpO6wzPWlt+p9sbzp1Y/b85r2uqKfxel0+eb2kteaTEumWzafLIy2ZT0qkWzafLo+0ZD4pkW7ZfLo80lJpPhsIWi+f3t8fu9+dmv0RgYH/jr5BHLPjK1/xQwbxe1qTHywYs9/GCpnSmxZcV5+dwuuV+UzZQPPRlMzHfDQBbbV+zEcT0FbrZ1k+mwham+atOSJ4zP4K1eWDWrS1KZ/OjtnTp4HpLzgxWObGjNnZrk1fW9TjvF6bT6uToZz5DJG51JuP+WgC2mr9mI8moK3Wz7J8NhK0aii1Wy1qvUPmYz6agLZaP+ajCWir9WM+moC2Wj/L8nHQqnkXsVrUGqP5mI8moK3Wj/loAtpq/ZiPJqCt1s+yfHYA7sMMrAFrwBqwBqwBa8AasAZq1oCftOo/EopYIQD/GyZgPsNsYDEf89EEtNX6MR9NQFutH/PRBLS1tH4ctGreRaylN63IpCpyYj56M8zHfDQBbbV+zEcT0Fbrx3w0AW0trR8HrZp3EWvpTSsyqYqcmI/eDPMxH01AW60f89EEtNX6MR9NQFtL68dBq+ZdxFp604pMqiIn5qM3w3zMRxPQVuvHfDQBbbV+zEcT0NbS+nHQqnkXsZbetCKTqsiJ+ejNMB/z0QS01foxH01AW60f89EEtLW0fnb4mdLjnl/cv28Ox+6Hhs6/aHT9Uv/bF/cfTp1Zlp5Ux/m5IH5cAL9vn87vXN43yVIubvGrWDv+8EB/JPyQwWnwhwzQfsze9zk/n/6YJWveToeW8f7Q46rtWl9nov6gmtyuretHLq6A0Xw0RPMxH01AW60f89EEtLW0fnbH/S4Eqpdfj9qHaA9BaxKj9mZYelLdATAnzIHBNILG3fmnVrvtYunUHPbHhj1aC+r3zV4Erb2fHT3um30AMGZvx2pz8/Jpx5klh4B1f2yI/xygRrbSftFT+4dQX1+Y86b5zAK969R8ujzSkvmkRLpl8+nySEvmkxLpls2nyyMtmU9KpFsuzWeHgKQT3OHJZahDgBhitu5srqXSk+oMksznbMvVhU54Upqb8yUAT3+2NXRscrZYF/Psl6uj7ZLOyqc7VPESWIa/YXr+pT23T5m6LfPpAZmhwnw0VPMxH01AW60f89EEtNX6WZbPLj5FvAzdDcJkUHKd6+Kblgl8bthgy0WsCEjPwXh3fbd+yAz4vQXuY/aOs7awOJ926AdzgtXZ84g9uxf9Ptvl8yDeid3NR4MyH/PRBLTV+jEfTUBbrZ9l+fSftJ6fNrZP185PLY/ddxq7b7Qu/d+7fF0gD+o833SCTXN+T/XyxLAfNN084X3XTMB7e0VizH5z1M1sV9QI9A/NMbzju9sfbq8K4H1oac8G+eDf6guktsunu89zlcxHkzUf89EEtNX6MR9NQFutn2X5nN9pbd9hvb4vegsqru8gHk63VwjO73SG1wcw3UU2DQHQ9QNW7TuSKSw+TU3qO0/8HLQmdIaLZD60/2P2Jn2HNdXXZehF9DO8yuot5qO3yHzMRxPQVuvHfDQBbbV+luVz/vaAw37X8JsB9ofj+cNKw+8xXp50RvvDm3b+MM++2eNDUjgOyXu2HSZvzSn5cNTNDD9xYldD9xUHB603XmOZc1CaftNC2P8x+9k//pDQ+npYP2Pr2LjdfPQGmo/5aALaav2YjyagrdbPsnwy39Mqgrrr3LpB4EJPWjtcQuAU6tN5nU29QFasD0FY8hQZPm5+x+xhLjG7XVHnWd14ZD+4FnhFCLd83+d2+dwWNWvGfDRe8zEfTUBbrR/z0QS01fpZlk8/aEVgdnuv8/LOYvcV0X7AOOumZd8j7c/h/H5l5quszl+Plf0e19y3IvQDqq7fMXt+82blkx+yUG2Oc6yLeQ6Zq6Pt+mG3m74u9dvlE9Y1Y9Z8NFzzMR9NQFutH/PRBLTV+lmWzw7fgXr7H/W3yxfBx5gCQd/+yHdaL/81n35Yad5NQ6CIALP9Yi68V9t7IjrwFLSPMxd4tq3O7+ze3uG8/pBAADJmbz21uXn5tOPMksMfDfF7Wo/77ofVpD0JYDP6wpw3zWcW6F2n5tPlkZbMJyXSLZtPl0daMp+USLdsPl0eacl8UiLdcmk+O3zNU/vOYfwSfw4sfo3q2qT0pDjyLX3rvxfZhrCcRHxCfOuZyaRBa1q+BqrXp7N46twda8zeH3J2Pv0hi9a8hW+PyL1vLO2j+nLQOrZZW9fP2PoetZuPJmg+5qMJaKv1Yz6agLaW1k//9QA9ftZaelLZQTZcaT5688zHfDQBbbV+zEcT0Fbrx3w0AW21fpbl46BV8y5itag1RvMxH01AW60f89EEtNX6MR9NQFutn2X57ADchxlYA9aANWANWAPWgDVgDdSsAT9p1X8kFLFCAP43TMB8htnAYj7mowloq/VjPpqAtlo/5qMJaGtp/Tho1byLWEtvWpFJVeTEfPRmmI/5aALaav2YjyagrdaP+WgC2lpaPw5aNe8i1tKbVmRSFTkxH70Z5mM+moC2Wj/mowloq/VjPpqAtpbWj4NWzbuItfSmFZlURU7MR2+G+ZiPJqCt1o/5aALaav2YjyagraX146BV8y5iLb1pRSZVkRPz0ZthPuajCWir9WM+moC2Wj/mowloa2n97M6/8JT+zGn4BaimSb9M/5h82f78HxQZn2PTvJ0OzZ7r2B/aX/k687z80tfubN83h9tPgOVgj613zN73WXrT+iPMW6PZYuwrk45uOKdx9lvnw5XOlZqPJms+5qMJaKv1Yz6agLZaP8vyOQet2VjjOo/ez5biJ1STDnNvGuaQDNmlhIA1/tTotXz5zD5+SnQXAtVLeT8QuI6td8zendilNDef3JjF6iRbxKv4tbJ9czweuj/vep7ANPab5lMM9LAj8xlmA4v5mI8moK3Wj/loAtpq/SzLZ3c67ERAmP7EKSbXr5t70/Qcm+Z02CdPVgPEt+M5oO186VSu7tylv7buesfsYdyQnZtPGKp4VrI9x6zH5gS4p0zQmuOcqdsyn+LAMw7NJwMlVJlPgJHJmk8GSqgynwAjkzWfDJRQZT4BRiZbms9OBiWZAANzSoPI0pNK1y3nmAmiO/2xht5j2lzwiQgsE+DG9Y7ZOwO3hbn5tCOVzg1wyg0zFLROYL9dPjkQ5evMRzM1H/PRBLTV+jEfTUBbrZ9l+ZyD1sOx+z7oiXPIBSLn2K773/VzbxqC1sE5ImjFO6yHfXN5Z3XX7PaHy9M/rCMbaCIYyzydHVvvmJ3cknRuPslwBYsjbONIOTYT2W+XTwQwX958NFvzMR9NQFutH/PRBLTV+lmWzw7ve+JJJP/7/PzO5v76YatcILJ40Hp9B3VojgiM8AGrIXuTvsOKDw3tmp2DVq00WEfZBhdZrUxj75M+cMxkzScDJVSZT4CRyZpPBkqoMp8AI5M1nwyUUGU+AUYmW5pP5iuvEGhcn0JmAxHEMoWftJ4/7LNv9vvrceh/Q0GXRZjjObA6NLenw+eGwX4u44khAtXLsT8cLx8eYqRO52PrHbPTT5KW3rTE/XzFSWyvww+wOb8TPMJ+s3zmI9/xbD4dHL2C+fSQdCrMp4OjVzCfHpJOhfl0cPQK5tND0qkozScTtIYPNiFo4VPXMI30HdPSkwpDDWbbOeTfu2ztORf5PvlXCd7PIx1xDT7pHO4r5zll2Q4GrenIfZ/b5ZOubZ6y+Wiu5mM+moC2Wj/mowloq/WzLJ8d3gcdfkrZDzC6n6a/THbeTbu8Vzk8x/SpKuaUqwtgEYz3PiAE+9h6x+xhjJCdl08YqHg2xzFXN/DtAbn5ZNhvl09ugeXrzEczNR/z0QS01foxH01AW62fZfmcv/Jqf+Q7rW/N6bjvfN9m73tJ8YGnJOCbe9PwDqqa4/nrluL3tHbWkARZb5cvu0+WcKM+tt4x+81RyMzNJwxVPosnqINsw3DZJ63T2G+aT0AwV9Z8NFnzMR9NQFutH/PRBLTV+lmWz+78VDJ88h7ve3Zf9Ux/AYoBbjvR+TftrfPtAP054j3b9hsQevY3vMPKd1rxPm5cYfr0dGy9Y/aWC3Pz8+FI86TDbBGUkmtMw9N7yf4y363zmYd669V8Wha5nPnkqLR15tOyyOXMJ0elrTOflkUuZz45Km1daT7Zd1rb4ablSk9q2qjbaWU+eq/Mx3w0AW21fsxHE9BW68d8NAFttX6W5eOgVfMuYrWoNUbzMR9NQFutH/PRBLTV+jEfTUBbrZ9l+ewA3IcZWAPWgDVgDVgD1oA1YA3UrAE/adV/JBSxQgD+N0zAfIbZwGI+5qMJaKv1Yz6agLZaP+ajCWhraf04aNW8i1hLb1qRSVXkxHz0ZpiP+WgC2mr9mI8moK3Wj/loAtpaWj8OWjXvItbSm1ZkUhU5MR+9GeZjPpqAtlo/5qMJaKv1Yz6agLaW1o+DVs27iLX0phWZVEVOzEdvhvmYjyagrdaP+WgC2mr9mI8moK2l9eOgVfMuYi29aUUmVZET89GbYT7mowloq/VjPpqAtlo/5qMJaGtp/WwkaB3/cYEbNvwy0y58uT0+yIJfyNrFL7/f9X7V69a/SX88YOzHFlJ764m50ptGv8uk87PfNp/5d8F8NGPzMR9NQFutH/PRBLTV+lmWzyaC1svPuPIT+Jefmt1nf4f11Bz2+2afCVqzzTOsez/Tetw3cawxe8blpj/9vQR7n/Q51bR15tOyyOXMJ0elrTOflkUuZz45Km2d+bQscjnzyVFp60rz2UDQmv7MKmDk6i5PVA+nvg2B17Sgtd+3O9aYvd2omCu9adH3vPnp60Uwfy/77fKZlz69mw9J5FPzyXNhrfmQRD41nzwX1poPSeRT88lzYW1pPtsNWvfpf8vjKSvq+oHW6bBvjnxQS5K59O3Y7Ht+m+YW9I7Zcz43/T2bfZbnIL7H6DH2pUU9sA2brTYfvXXmYz6agLZaP+ajCWir9bMsnw0ErZcnqPvjqbnEndfXA5IotA1M+4EWbIfjodnzvdZ9953XG3K8D5t5JHt5iogHvCP2m6NuZsuixtrnZr9lPt2dnqdkPpqr+ZiPJqCt1o/5aALaav0sy2cTQWvT+3AUA9grLDwBvQWbadD61hz3u7OdD1vP76X2nhZOCEpfMGhdgr1P+mVPej3a9qzWj94z8zEfTUBbrR/z0QS0tbR+6ghaT4dmjw9Q8TjE//pH0In3JRlyNs3buX3bpn3KCnhp0JoDevGZPKwdf5L6ckHrMuxLizq341uuMx+9e+ZjPpqAtlo/5qMJaKv1syyfOoJWtebOU1Q2DEEnAthO9DklaEV8mnnPdfCd1WvbMTunl6SbFfVC7DfLJ9nnuYrmo8maj/loAtpq/ZiPJqCt1s+yfLYRtPb+K78NWvEhqd53sF7fXb28MYAPCaXvsLb9u7hzAW+si3n2zNXRdkk3K+pskN6yK8V+s3y62zxbyXw0WvMxH01AW60f89EEtNX6WZZP/UHr+b/78ZVV3dcDdr1AluD6QSSCq/TDRLkPXMHD+X3XA9+Zvf7QwO192XE7ZxHT7YoaLOdnv10+cZfny5uPZms+5qMJaKv1Yz6agLZaP8vy2UDQikgST0vDE1U8OW1j2IRYP2jFh4mOh/ZXsfadd2bT9ukvYjGA5TBjdrZr002Lelb2F0ab5tNu82w589Fozcd8NAFttX7MRxPQVutnWT7bCFo1k+qtFrXeIvMxH01AW60f89EEtNX6MR9NQFutn2X5OGjVvItYLWqN0XzMRxPQVuvHfDQBbbV+zEcT0FbrZ1k+OwD3YQbWgDVgDVgD1oA1YA1YAzVrwE9a9R8JRawQgP8NEzCfYTawmI/5aALaav2YjyagrdaP+WgC2lpaPw5aNe8i1tKbVmRSFTkxH70Z5mM+moC2Wj/mowloq/VjPpqAtpbWj4NWzbuItfSmFZlURU7MR2+G+ZiPJqCt1o/5aALaav2YjyagraX146BV8y5iLb1pRSZVkRPz0ZthPuajCWir9WM+moC2Wj/mowloa2n9OGjVvItYS29akUlV5MR89GaYj/loAtpq/ZiPJqCt1o/5aALaWlo/Gwla0y/0PzbdjzaN2U/Ncc8fF9g3h2O3dxf5mK8xe9cbSqU3rT9CzTXjvF6bz/jemY9mZD7mowloq/VjPpqAtlo/y/LZRNB6/hnW+NOqx30Tf8ZV29+a434XAtVLeT8QuPZ+xvW4b/bqZ1wTe277XlnUYzzB65X55PSS1plPSqRbNp8uj7RkPimRbtl8ujzSkvmkRLpl8+nySEul+WwgaE1/ZhVIYl3ME1eoezs2+33yZDZXd+4a+tHVe8a69elmSm9a13vNpTGel7m/Lp9pe2c+mpP5mI8moK3Wj/loAtpq/SzLZwNBaw5ILhiK7YIdAWp4UnppFeyx20Awiye5Zxdj9ugr5F9W1BN5vSyfoBGVNR9Fx0/qNR3zMZ8xAtru64/5aALaWlo/2wxaT4dMIBrARXs2cELQum96bwicDs2uF+A2Df6L+1w9Zg9TiNnSmxZ9V52fyOtl+UzcPPPRoMzHfDQBbbV+zEcT0FbrZ1k+2wtas0FogNazp++w4oNBu2bnoDVAmynroLUIWF8UNUbzMR9NQFutH/PRBLTV+lmWz7aC1l5AmsAatJ+awx6B6uXYH47NYe8nrQm98kUHrUWY+qKoMZqP+WgC2mr9mI8moK3Wz7J8thO0DgakV2Bj9g7X977Teg1wB8Y4HTIBcBjvZUU9kdfL8gkaUVnzUXT8zqamYz7mM0ZA2339MR9NQFtL62cbQetA8HNDNWa/Nbxm0D7z7mr3WwnYKQa4MZ+zs66blt60rveaS9N4vS6faXtnPpqT+ZiPJqCt1o/5aALaav0sy2cDQSveSVVPMt9pf7v80EA2ZsV3huI7YON3wh4uZW7LmJ3tYvrKop7C65X5RJ0M5c1niMyl3nzMRxPQVuvHfDQBbbV+luVTf9CKp6LXd1H5TuolDf9lr+zg+YZ3WPlOK74JIP4iVvo0MP0Fp9PIr2+l9v4Gvraox3j6vy/7iunWvLZ+uixyJfPJUWnrzKdlkcuZT45KW2c+LYtcznxyVNq60nzqD1rbtW82V3rTNgtiYOLmMwDmWm0+5qMJaKv1Yz6agLZaP+ajCWhraf04aNW8i1hLb1qRSVXkxHz0ZpiP+WgC2mr9mI8moK3Wj/loAtpaWj87OPRhBtaANWANWAPWgDVgDVgDNWvAT1r1HwlFrBCA/w0TMJ9hNrCYj/loAtpq/ZiPJqCt1o/5aALaWlo/Dlo17yLW0ptWZFIVOTEfvRnmYz6agLZaP+ajCWir9WM+moC2ltaPg1bNu4i19KYVmVRFTsxHb4b5mI8moK3Wj/loAtpq/ZiPJqCtpfXjoFXzLmItvWlFJlWRE/PRm2E+5qMJaKv1Yz6agLZaP+ajCWhraf04aNW8i1hLb1qRSVXkxHz0ZpiP+WgC2mr9mI8moK3Wj/loAtpaWj8VBa3XL6HP/FTV+VeV0h8QyLQ7ozsdmt3u0JzIcezHCdjulqZfhn8c+XGB1H5zdMuU3rSb401kxnj6g0Zj2/ja+hmjY/2MEbJ+NCHzMR9NQFutn2X51BG0vp2aA36q9Xg4/4RqigBB61CM2m178bOPQWu3wbWEdvlgs/ezo8d9sw+Dj9lzw72yqKfwemU+Ob2kdeaTEumWzafLIy2ZT0qkWzafLo+0ZD4pkW7ZfLo80lJpPlUErW+nY3P+ZVU8JQ0BIhd/OuwmBa2X4Db9WVZ6adPTYSgIzvWNdTFPf7k62i5p6U3req+5lGPTr3tdPtP2znw0J/MxH01AW60f89EEtNX6WZZPFUHrbcmDQeu+OY5+1SmfnvaDopt/ZPC6QCYwPreBLfME9hY0j9k7A7WFlxX1RF4vy6eViMyZj8Tj77HVeMzHfEYIaLOvP+ajCWhraf38f2gNHQ4AKyQZAAAAAElFTkSuQmCC"
    }
   },
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算确定 应暂估入库数\n",
    "在“确定应暂估数” 的 表中 添加公式，计算应暂估数，需要手动添加一些数据；单价来自于上月暂估入库时候的单价数据，不过这次可以改一下，用那个当月的采购入库时的单价\n",
    "![image.png](attachment:image.png)\n",
    "\n",
    "确定应暂估入库数sheet和暂估入库数据源sheet的区别是， 删除了不需要暂估入库的数据（应暂估入库数=0 的数据）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 将数据从暂估入库数据源 写入 采购入库单 模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "采购入库单=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\0.采购入库单模版-商贸.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "数据源=wb暂估底稿.sheets('模版数据源').range('a1').expand().options(pd.DataFrame).value\n",
    "数据源=数据源.reset_index()\n",
    "ws2=wb暂估底稿.sheets('采购入库单')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ws2.range('a5:aa10000').clear_contents()\n",
    "ws2.range('g3:i3').value=数据源.loc[:,['供应商编码','供应商','部门编码','仓库名称']].values\n",
    "ws2.range('m3:o3').value=数据源.loc[:,['备注','仓库编码','仓库名称']].values\n",
    "ws2.range('r3:s3').value=数据源.loc[:,['存货编码','存货名称']].values\n",
    "ws2.range('u3:v3').value=数据源.loc[:,['计量单位','本月应暂估入库数']].values\n",
    "ws2.range('y3').options(transpose=True).value=数据源['合计金额'].values\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws2.range('a3').value='2024-9-30'\n",
    "ws2.range('b3').value='1'\n",
    "ws2.range('c3').value='01'\n",
    "ws2.range('d3').value='普通采购'\n",
    "ws2.range('z3').value=0.13\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
